﻿#include "data.h"

Db_Data::Db_Data()
{

}

QSqlDatabase Db_Data::init_database(QString db_type,QString db_name,
                                     QString caller_type)
/*
 * 功能：初始化数据库
 * db_type: 数据库的类型，
 * db_name :新建数据库的名称
 * caller_type: 调用者类型。若为创建用户信息则为 user;若为创建账簿，则为 table
 * 返回的是新建的数据库地址
 */
{
    QSqlDatabase db ;
    db = QSqlDatabase::addDatabase(db_type);
    //这里输入你的数据库名
    db.setHostName(db_name);
    db.setDatabaseName(db_name+".db");
    if (!db.open()){
         add_log("Db_Data::creat_database", QStringLiteral("数据库创建失败！"));
    }
    QSqlQuery query(db);
    // 创建登录用户信息数据库
    if(caller_type == "user")
    {
        bool ok = query.exec(CREATE_USER);
        if (!ok)
            add_log("Db_Data::creat_database", QStringLiteral("初始化用户数据库失败！"));
        else
            add_log("Db_Data::creat_database", QStringLiteral("用户信息数据库创建成功！"));
        return db;
    }
    else {
        //创建登录用户信息存储数据的数据库
        bool ok = query.exec(CREATE_PERSON);
        ok = query.exec(CREATE_GROUP);
        ok = query.exec(CREATE_RELATION);
        if (!ok)
            add_log("Db_Data::creat_database", QStringLiteral("初始化存储数据库失败！"));
        add_log("Db_Data::creat_database", QStringLiteral("账簿数据库创建成功！"));

        return db;
    }
}


bool Db_Data::check_table(QSqlDatabase &db,QString table_name)
/*
 * 根据提供的用户信息，查找是否已经创建表；
 * return:
 * true:存在同名用户
 * false:不存在同名用户
 */
{
    QSqlQuery query(db);
    bool val = false;
    while(query.next()){
        QString t_name = query.value(0).toString();
        if(t_name == table_name)
            val = true;
    }
    return val;
}

bool Db_Data::check_user(QSqlDatabase &db,QString user_name)
/*
 * 根据提供的用户信息，查找是否已经创建用户；
 * return:
 * true:存在同名用户
 * false:不存在同名用户
 */
{
    QSqlQuery query(db);
    bool val = false;
    query.exec(SELECT_USER) ;//(id int primary key, name varchar(20),password VARCHAR(8) )");
    while(query.next()){
        QString t_name = query.value(0).toString();
        if(t_name == user_name)
            val = true;
    }
    return val;
}

char Db_Data::insert_user_info(QSqlDatabase &db,QString user_name, QString user_password)
/*
 * 插入用户信息；
 */
{
    char val = 0;
    if(check_user(db,user_name)) //创建之前就存在的话，就不能再创建
        val= I_EXIST ;
    else{
        QSqlQuery query(db);
        query.exec(QString ("insert into user_info(name,password) values('%1','%2')").arg(user_name).arg(user_password));
        if(check_user(db,user_name)) //创建好后需要再次查询验证,存在则创建成功
            val = I_SUCCESS;
        else
            val = I_FALSE;
    }

    return val;
}

QStringList Db_Data::find_groups(QSqlDatabase &db)
/*
 * 根据用户信息返回存在的账簿分组
 *
 */
{
    QStringList m_list;
    QSqlQuery query(db);
    query.exec(SELECT_GROUP);
     while(query.next()){
         QString t_name = query.value(0).toString();
         m_list.append(t_name);
     }
    return m_list;
}


/*
**************************************以下部分为 插入/删除 数据 的业务处理部分**************
*/
bool Db_Data::insert_check(QSqlDatabase &db, QString table ,
                            QString check_row, QString check_data)
/*
 * 所有数据在插入之前都需要检查是否有重复项
 */
{
     QSqlQuery query(db);
     bool val = false;
     query.exec(QString("select %1 from %2").arg(check_row).arg(table));
     while(query.next()){
         QString t_data = query.value(0).toString();
         if(t_data == check_data){
             val = true;
             return val;
         }
     }
     return val;
}

char Db_Data::insert_group_data(QSqlDatabase &db, QString Data)
/*
 *分组表插入新建组数据
 */
{
    if(!insert_check(db,"m_group", "group_name" , Data)){
        QSqlQuery query(db);
        bool ok = query.exec(QString ("insert into m_group(group_name) values('%1')").arg(Data));
        if(!ok){
           add_log("Db_Data::insert_group_data", QStringLiteral("插入组数据失败！"));
           return I_FALSE;
        }
        else{
           add_log("Db_Data::insert_group_data", QStringLiteral("插入组数据成功！"));
           return I_SUCCESS;
        }
    }
    else{
       add_log("Db_Data::insert_group_data", QStringLiteral("插入组数据失败！已经存在相同的组"));
       return I_EXIST;
    }
}

char Db_Data::delate_group_data(QSqlDatabase &db, QString Data)
/*
 *分组表删除新建组数据
 */
{
    if(insert_check(db,"m_group", "group_name" , Data)){
        QSqlQuery query(db);
        bool ok = query.exec(QString ("DELETE FROM m_group where group_name = '%1'").arg(Data));
        if(!ok)
        {
           add_log("Db_Data::insert_group_data", QStringLiteral("删除组数据失败！"));
           return I_FALSE;
        }else
        {
           add_log("Db_Data::insert_group_data", QStringLiteral("删除组数据成功！"));
           return I_SUCCESS;
        }
    }
    else{
       add_log("Db_Data::insert_group_data", QStringLiteral("删除组数据失败！没有查找到同名组"));
       return I_NOT_EXIST;
    }
}

void Db_Data::insert_person_data(QSqlDatabase &db, QString Data)
/*
 *分组表插入新建组数据
 */
{
    if(!insert_check(db,"person", "name" ,Data)){
        QSqlQuery query(db);
        bool ok = query.exec(QString("insert into person(name) values ('%1')").arg(Data));
        if(!ok)
           add_log("Db_Data::insert_person_data", QStringLiteral("插入人员数据失败！"));
        else
           add_log("Db_Data::insert_group_data", QStringLiteral("插入人员数据成功！"));
    }
    else
        add_log("Db_Data::insert_group_data", QStringLiteral("插入人员数据失败！，存在相同的人名"));
}

char Db_Data::insert_relation_data(QSqlDatabase &db,
                                   QString person,
                                   QString amount,
                                   QString group,
                                   QString date,
                                   QString remark)
/*
 *插入关系数据精确的数据
 */
{
//    if( !insert_check(db,"relation", "personName", person)
//            && !insert_check(db,"relation", "time", date)
//            && !insert_check(db,"relation", "amount", amount))
//    {
        QSqlQuery query(db);
        QString order = QString("insert into relation(personName,amount,time,groupName,remark)"
                                 "values('%1','%2','%3','%4','%5')").arg(person).arg(amount).arg(date).arg(group).arg(remark);
        bool ok = query.exec(order);
        if(!ok){
           add_log("Db_Data::insert_relation_data", QStringLiteral("插入关系数据失败！"));
           return I_FALSE;
           }
        else
            return I_SUCCESS;
//    }
//    else{
//        add_log("Db_Data::insert_relation_data", QStringLiteral("存在相同的关系数据！"));
//        return I_EXIST;
//    }
}

char Db_Data::delete_relation_data_accurate(QSqlDatabase &db,
                                   QString person,
                                   QString amount,
                                   QString group,
                                   QString date,
                                   QString remark)
/*
 *删除关系数据精确的数据
 */
{
    if( insert_check(db,"relation", "personName", person)
            && insert_check(db,"relation", "time", date)
            && insert_check(db,"relation", "amount", amount)
            && insert_check(db,"relation", "groupName", group)
            && insert_check(db,"relation", "remark", remark))
    {
        QSqlQuery query(db);
        QString order = QString("delete from relation where personName = '%1' and amount = '%2' "
                                 "and time = '%3' and groupName = '%4'").arg(person).arg(amount).arg(date).arg(group);
        bool ok = query.exec(order);
        if(!ok){
           add_log("delete_relation_data_accurate", QStringLiteral("删除关系数据失败！"));
           return I_FALSE;
           }
        else
            return I_SUCCESS;
    }
    else{
        add_log("delete_relation_data_accurate", QStringLiteral("不存在数据！"));
        return I_NOT_EXIST;
    }
}

void Db_Data::delete_relation_data_match(QSqlDatabase &db,QString row_name, QString match_str)
/*
 *删除关系数据中某一个匹配的内容
 */
{
    if(insert_check(db,"relation", row_name, match_str))
    {
        QSqlQuery query(db);
        QString order = QString("delete from relation where %1 = '%2'").arg(row_name).arg(match_str);
        query.exec(order);
    }
}

/*
**************************************以下部分为 查找数据 的业务处理部分**************
*/
float Db_Data::count_table_data(QSqlDatabase &db, QString table)
/*
 * 统计表中所有记录的数据
 */
{
    QSqlQuery query(db);
    float number = 0;
    query.exec(QString("select * from %1").arg(table));
    while(query.next()){
          number += 1;
    }
    return number;
}

QList<float> Db_Data::count_amount_all(QSqlDatabase &db)
/*
 * 统计表中所有 送出/收入 的金额
 * 返回一个统计的列表【收入，送出】
 */
{
    QList<float> total_list;
    QSqlQuery query(db);
    float positive_num = 0,negative_num = 0;
    query.exec(QString("select amount from relation"));
    while(query.next()){
          float temp = query.value(0).toFloat();
          if(temp >= 0)
              positive_num += temp;
          else
              negative_num += temp;
    }
    total_list.append(positive_num);
    total_list.append(negative_num);
    return total_list;
}

QList<float> Db_Data::count_amount_group(QSqlDatabase &db, QString group_name)
/*
 * 统计表中某个组的所有 送出/收入 的金额
 * 返回一个统计的列表【收入，送出】
 */
{
    QList<float> total_list;
    QSqlQuery query(db);
    float positive_num = 0,negative_num = 0;
    query.exec(QString("select amount from relation where groupName = '%1'").arg(group_name));
    while(query.next()){
          float temp = query.value(0).toFloat();
          if(temp >= 0)
              positive_num += temp;
          else
              negative_num += temp;
    }
    total_list.append(positive_num);
    total_list.append(negative_num);
    return total_list;
}

QList<QString> Db_Data::select_relation_items(QSqlDatabase &db, QList<QString> row_name, QList<QString> condition)
/*
 *传入的是两个列表，对应着需要查找的条件。
 *
 */
{
    QList<QString> temp_list;
    QSqlQuery query(db);
    QString order = "select * from relation where ";
    QString all_condition;
    int size = row_name.size();
    all_condition = row_name[0] + "=" + QString("'%1'").arg(condition[0]);
    order  = order + all_condition;
    for(int i=1;i<size;i++){
        all_condition = row_name[i] + "=" + QString("'%1'").arg(condition[i]);
        order  = order + "and " + all_condition;
    }
    query.exec(order);
    int count =0;
    while(query.next()){
          count += 1;
          QString temp_name = query.value(1).toString();
          QString temp_time= query.value(2).toString();
          QString temp_amount = query.value(3).toString();
          QString temp_group = query.value(4).toString();
          QString temp_remark = query.value(5).toString();
          QString out = QString::number(count)+":"+QStringLiteral(" 姓名:")+temp_name+
                   QStringLiteral(" 时间:")+temp_time+QStringLiteral(" 金额:")+temp_amount+
                   QStringLiteral(" 账簿:")+temp_group+QStringLiteral(" 备注:")+temp_remark;
          temp_list<<out;
    }
    return temp_list;
}

float Db_Data::count_amount_date(QSqlDatabase &db, QString date_begin, QString date_end)
/*
 * 统计表中某个组的所有 送出/收入 的金额
 * 返回一个统计的列表【收入，送出】
 */
{

    QSqlQuery query(db);
    float total = 0;
    QString order = QString("select amount from relation where time >= '%1' and time <= '%2' ").arg(date_begin).arg(date_end);
    query.exec(order);
    while(query.next()){
          float temp = query.value(0).toFloat();
          total += temp;
    }
    return total;
}

